-- -------------------------------------------------
--    Project Name:  智兔APP到发货量实际汇总
--    File Name:     jms_dm.dm_to_deliver_real_sum_app_dt
--    Job Name:      jms_dm__dm_to_deliver_real_sum_app_dt
--    Description:   智兔APP到发货量实际汇总
--    Author:        leichao
--    date:          2022-04-15 10:30
-- -------------------------------------------------

with t_route as (
    select *
    from (
        SELECT
            waybill_no                                                                                                          -- 运单号
            ,send_station_number                                                             as step                            -- 站点序号
            ,send_station_network_code                                                       as network_code                    -- 站点编码
            ,lead(send_station_network_code) over(partition by waybill_no order by send_station_number desc) as prev_network_code   -- 上一站点编码
            ,nvl(send_station_loading_next_network_code,send_station_send_next_network_code) as loading_send_next_network_code  -- 站点最晚装车发件下一站站点编码
            ,nvl(send_station_arrival_early_time,send_station_unloading_early_time)          as arrival_early_scantime          -- 站点最早卸车到件时间
            ,nvl(send_station_send_later_time,send_station_loading_later_time)               as send_later_scantime             -- 站点最晚装车发件时间
            ,send_station_unloading_arrival_early_shipment_no                                as unloading_arrival_shipment_no   -- 站点卸车到件任务号
            ,send_station_shipment_no                                                        as loading_send_shipment_no        -- 站点装车发件任务号
        FROM jms_dwd.dwd_wide_unsign_trace_waybill_step_dt
        where dt between date_add('{{ execution_date | cst_ds }}',-44)  and '{{ execution_date | cst_ds }}'
        and send_station_network_type in ('2','3','4')
        and (date_format(send_station_arrival_early_time,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
        or date_format(send_station_unloading_early_time,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
        or date_format(send_station_send_later_time,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
        or date_format(send_station_loading_later_time,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
        or date_format(next_station_arrival_early_time,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
        or date_format(next_station_unloading_early_time,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
        or date_format(next_station_send_later_time,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
        or date_format(next_station_loading_later_time,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
        )
    ) tmp
    where (date_format(arrival_early_scantime,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
        or date_format(send_later_scantime,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}')
),

t_network_final as (
select
    id
    ,code
    ,name
    ,type_id
    ,is_entrepot
    ,is_distribution_center
    ,regional_id
    ,regional_desc
    ,provider_id
    ,provider_desc
    ,agent_id
    ,agent_code
    ,agent_name
    ,franchisee_id
    ,franchisee_code
    ,franchisee_name
from jms_dim.dim_sys_network_detail_dt
where dt = '{{ execution_date | cst_ds }}' and is_delete = 1 and is_enable = 1
),
---t_shipment as (
---select
---    shipment_no
---from jms_ods.yl_tmsnew_tms_shipment
---where dt = '{{ execution_date | cst_ds }}' and transport_type = 'GX'
---group by shipment_no
---)
t_shipment as (
    select shipment_no
    from jms_dwd.dwd_tmsnew_shipment_union_base_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-10) and date_add('{{ execution_date | cst_ds }}',7)
      and transport_type = 'GX'
      and table_type=1
    group by shipment_no
)

insert overwrite table jms_dm.dm_to_deliver_real_sum_app_dt partition(dt)
select
    todeliver_date,
    todeliver_time,
    network_code,
    network_type,
    max(regional_id) as regional_id,
    max(regional_desc) as regional_desc,
    max(provider_id) as provider_id,
    max(provider_desc) as provider_desc,
    max(agent_code) as agent_code,
    max(agent_name) as agent_code,
    max(franchisee_code) as franchisee_code,
    max(franchisee_name) as franchisee_name,
    max(network_name) as network_name,
    sum(if(flight_type = '1' and is_gx = 1,1,0)) as main_deliver,
    sum(if(flight_type = '1' and is_gx = 0,1,0)) as branch_deliver,
    sum(if(flight_type = '2' and is_gx = 1,1,0)) as main_arrival,
    sum(if(flight_type = '2' and is_gx = 0,1,0)) as branch_arrival,
    todeliver_date as dt
from
(
select
    billcode,
    t_network_final.regional_id as regional_id,
    t_network_final.regional_desc as regional_desc,
    t_network_final.provider_id,
    t_network_final.provider_desc,
    t_network_final.agent_code,
    t_network_final.agent_name,
    t_network_final.franchisee_code,
    t_network_final.franchisee_name,
    this_station_code as network_code,
    t_network_final.name as network_name,
    case when temp.type_id=336 and temp.is_entrepot=2 and temp.is_distribution_center=2 then '1'
    when temp.is_entrepot= 1 then '2'
    when temp.is_distribution_center = 1 then '3' end network_type,
    date_format(todeliver_time, 'yyyy-MM-dd') todeliver_date,
    substring(todeliver_time,12,2) as todeliver_time,
    flight_type,
    case when t5.shipment_no is not null then 1
    when t_network_final.franchisee_code is null and temp.is_distribution_center = 1 then 1
    else 0 end as is_gx
from
(
select
    --网点到件
    waybill_no as billcode,
    network_code as this_station_code,
    prev_network_code as last_next_code,
    arrival_early_scantime as todeliver_time,
    "2" flight_type,
    unloading_arrival_shipment_no as shipment_no
from t_route
where arrival_early_scantime is not null and to_date(arrival_early_scantime) >= date_add('{{ execution_date | cst_ds }}',-3) and step != 1 and prev_network_code is not null and prev_network_code != ''

union all

select
    --网点发件
    waybill_no as billcode,
    network_code as this_station_code,
    loading_send_next_network_code as last_next_code,
    send_later_scantime as todeliver_time,
    "1" flight_type,
    loading_send_shipment_no as shipment_no
from t_route
where send_later_scantime is not null and loading_send_next_network_code is not null and loading_send_next_network_code != '' and to_date(send_later_scantime) >= date_add('{{ execution_date | cst_ds }}',-3)
) t1
left join t_network_final on t1.this_station_code = t_network_final.code
left join t_network_final as temp on t1.last_next_code = temp.code
left join t_shipment t5 on t1.shipment_no = t5.shipment_no
) t2
where network_type is not null
group by todeliver_date,network_code,todeliver_time,network_type
distribute by 1;